<?php

/**
 * Global model for using in this application
 * @package Model
 * @author Chivorn VANN
 * @version 1.0
 */
if (!defined('BASEPATH'))
	exit('Permission Denied!');

class Mod_tag extends CI_Model {
	function __construct() {
		parent::__construct();
	}

	// get all post count with ajax -> //View all record defualt
	public function get_all_post_count() {
		$this -> db -> select('COUNT(*) as t_records');
		$this -> db -> from(PREFIX_TABLE.'post_has_tags');
		$this -> db -> join(PREFIX_TABLE.'posts','use_post_id=post_id');
		$this -> db -> join(PREFIX_TABLE.'tags','tag_id = use_tag_id');
		$this -> db -> where(PREFIX_TABLE.'posts.status', 1);
		return $this -> db -> get();
	}

	// get result and view it as random tags
	public function select_tag_random() {
		$query = $this -> db -> query('SELECT key_tags,tag_id , '.PREFIX_TABLE.'tags.name as tage_name, count('.PREFIX_TABLE.'tags.name) as count_tag FROM '.PREFIX_TABLE.'post_has_tags 
		inner join '.PREFIX_TABLE.'posts on '.PREFIX_TABLE.'posts.post_id = '.PREFIX_TABLE.'post_has_tags.use_post_id inner join '.PREFIX_TABLE.'tags on 
		'.PREFIX_TABLE.'tags.tag_id = '.PREFIX_TABLE.'post_has_tags.use_tag_id GROUP BY '.PREFIX_TABLE.'post_has_tags.use_tag_id ORDER BY RAND() LIMIT 100');
		return $query -> num_rows() > 0 ? $query -> result() : null; 
	}
	

	// view when select on tags's name by clicking on id
	public function get_picture_tags_count() {
		$this -> db -> select('COUNT(*) as t_records');
		$this -> db -> from(PREFIX_TABLE.'post_has_tags');
		$this -> db -> join(PREFIX_TABLE.'posts','use_post_id=post_id');
		$this -> db -> join(PREFIX_TABLE.'tags','tag_id = use_tag_id');
		$this -> db -> where(PREFIX_TABLE.'posts.status', 1);
		$this -> db -> where('key_tags', $this->uri->segment(3));
		return $this -> db -> get();
	}
	
	//// get data when click on tag ////
	public function get_data_by_click_on_tag($uri_id_3,$nmber_sort,$items_per_group, $position){
		$this -> db -> select('*');
		$this -> db -> from(PREFIX_TABLE.'post_has_tags');
		$this -> db -> join(PREFIX_TABLE.'posts','use_post_id=post_id');
		$this -> db -> join(PREFIX_TABLE.'tags','tag_id = use_tag_id');
		$this -> db -> where(PREFIX_TABLE.'tags.key_tags', $uri_id_3);
		$this -> db -> where(PREFIX_TABLE.'posts.status', 1);
		$this -> db -> where("date(".PREFIX_TABLE."posts.created_date) between DATE_SUB( date(now()),INTERVAL ".$nmber_sort." DAY ) AND now()");
		$this -> db -> limit($items_per_group, $position);
		$this -> db -> group_by('post_id DESC');
		// 5,0 -> 0(start), 5 (end)
		return $this->db->get();
	}
	
	/// get data by search /////
	public function get_data_by_search($uri,$items_per_group, $position){
		$this -> db -> select('*');
		$this -> db -> from(PREFIX_TABLE.'post_has_tags');
		$this -> db -> join(PREFIX_TABLE.'posts','use_post_id=post_id');
		$this -> db -> join(PREFIX_TABLE.'tags','tag_id = use_tag_id');
		$this -> db -> like(PREFIX_TABLE.'tags.name', $uri);
		$this -> db -> where(PREFIX_TABLE.'posts.status', 1);
		$this -> db -> order_by(PREFIX_TABLE."posts.post_id");
		$this -> db -> limit($items_per_group, $position);
		$this -> db -> group_by('post_id DESC');
		return $this->db->get();
	}
	
	//// get data by time ////
	public function get_data_by_time($uri,$items_per_group, $position){
		$this -> db -> select('*');
		$this -> db -> from(PREFIX_TABLE.'post_has_tags');
		$this -> db -> join(PREFIX_TABLE.'posts','use_post_id=post_id');
		$this -> db -> join(PREFIX_TABLE.'tags','tag_id = use_tag_id');
		$this -> db -> where(PREFIX_TABLE.'posts.status', 1);
		$this -> db -> where("date(".PREFIX_TABLE."posts.created_date) between DATE_SUB( date(now()),INTERVAL ".$uri." DAY ) AND now()");
		$this -> db -> limit($items_per_group, $position);
		$this -> db -> group_by('post_id DESC');
		return $this->db->get();
		
	}
	
	//// get data by search and sort ////
	public function get_data_by_search_and_sort($tag,$uri,$items_per_group, $position){
		$this -> db -> select('*');
		$this -> db -> from(PREFIX_TABLE.'post_has_tags');
		$this -> db -> join(PREFIX_TABLE.'posts','use_post_id=post_id');
		$this -> db -> join(PREFIX_TABLE.'tags','tag_id = use_tag_id');
		$this -> db -> like(PREFIX_TABLE.'tags.name', $tag);
		$this -> db -> where(PREFIX_TABLE.'posts.status !=', 0);
		$this -> db -> where("date(".PREFIX_TABLE."posts.created_date) between DATE_SUB( date(now()),INTERVAL ".$uri." DAY ) AND now()");
		$this -> db -> order_by(PREFIX_TABLE."posts.post_id");
		$this -> db -> limit($items_per_group, $position);
		$this -> db -> group_by('post_id DESC');
		return $this->db->get();
	}
	// view when select on tag's name by clicking on id and sort time
	
	public function get_picture_tag_count_by_id_and_time($time_number){
		$this -> db -> select('COUNT(*) as t_records');
		$this -> db -> from(PREFIX_TABLE.'post_has_tags');
		$this -> db -> join(PREFIX_TABLE.'posts','use_post_id=post_id');
		$this -> db -> join(PREFIX_TABLE.'tags','tag_id = use_tag_id');
		$this -> db -> where(PREFIX_TABLE.'tags.key_tags', $this -> uri -> segment(3));
		$this -> db -> where("date(".PREFIX_TABLE."posts.created_date) between DATE_SUB( date(now()),INTERVAL ".$time_number." DAY ) AND now()");
		$this -> db -> where(PREFIX_TABLE.'posts.status', 1);
		return $this -> db -> get();
	}

	// get count all record using search box
	public function get_tags_by_box_count($keyword) {
		$this -> db -> select('count(*) as t_records');
		$this -> db -> from(PREFIX_TABLE.'post_has_tags');
		$this -> db -> join(PREFIX_TABLE.'posts','use_post_id=post_id');
		$this -> db -> join(PREFIX_TABLE.'tags','tag_id = use_tag_id');
		$this -> db -> like(PREFIX_TABLE.'tags.name', $keyword);
		$this -> db -> where(PREFIX_TABLE.'posts.status', 1);
		return $this -> db -> get();
	}
	
	// get count all records
	public function get_count_row($items_per_group) {
		$results = $this -> db -> query("SELECT COUNT(*) as t_records FROM ".PREFIX_TABLE."posts inner join ".PREFIX_TABLE."tags on
		".PREFIX_TABLE."posts.use_tag_Id = ".PREFIX_TABLE."tags.tag_id where ".PREFIX_TABLE."posts.status != 0
		");
		$total_records = $results -> row() -> t_records;
		$total_groups = ceil($total_records / $items_per_group);
		return $total_groups;
	}
	
	// select record and view result as autocomplete
	public function auto_complete_search($keyword) {
		$this -> db -> select('*');
		$this -> db -> from(PREFIX_TABLE.'tags');
		$this -> db -> where(PREFIX_TABLE.'tags.status !=', 0);
		$this -> db -> like('name', $keyword);
		$query = $this -> db -> get();
		return $query -> num_rows() > 0 ? $query -> result() : null;
	}
	
	// select count all post base on time with one parameter
	public function get_tags_sort_by_time_count($parm) {
		$this -> db -> select('count(*) as t_records');
		$this -> db -> from(PREFIX_TABLE.'post_has_tags');
		$this -> db -> join(PREFIX_TABLE.'posts','use_post_id=post_id');
		$this -> db -> join(PREFIX_TABLE.'tags','tag_id = use_tag_id');
		$this -> db -> where("date(".PREFIX_TABLE."posts.created_date) between DATE_SUB( date(now()),INTERVAL ".$parm." DAY ) AND now()");
		$this -> db -> where(PREFIX_TABLE.'posts.status', 1);


		return $this -> db -> get();
	}
	
	// select count all post base on time and tag
	public function get_tag_sort_by_time_tag_count($tag,$time_number){
		$this -> db -> select('count(*) as t_records');
		$this -> db -> from(PREFIX_TABLE.'post_has_tags');
		$this -> db -> join(PREFIX_TABLE.'posts','use_post_id=post_id');
		$this -> db -> join(PREFIX_TABLE.'tags','tag_id = use_tag_id');
		$this -> db -> like(PREFIX_TABLE.'tags.name',$tag);
		$this -> db -> where(PREFIX_TABLE.'posts.status', 1);
		$this -> db -> where("date(".PREFIX_TABLE."posts.created_date) between DATE_SUB( date(now()),INTERVAL ".$time_number." DAY ) AND now()");
		return $this -> db -> get();
	}
	
	// select tag last id
	public function select_tag_last_id() {
		$query = $this -> db -> query('SELECT tag_id, name as tag_name FROM '.PREFIX_TABLE.'tags where tag_id = (select max(tag_id) from '.PREFIX_TABLE.'tags)');
		return $query -> num_rows() > 0 ? $query -> result() : null; 
	}
	
	// select tag
	public function select_tag() {

		//$query = $this->mod_globals->select_all(PREFIX_TABLE."tags",10000);

		$query = $this -> db -> query("SELECT tag_id, name as tag_name FROM ".PREFIX_TABLE."tags order by tag_id desc");
		if ($query -> num_rows() > 0) {
			return $query -> result();
		} else {
			return null;
		}
	}
	// create new tag query
	public function insert_new_tag($get_id,$random_key){
	     $this->mod_globals->insert(PREFIX_TABLE."tags", array('name' => $get_id,'key_tags'=>$random_key));
	}
	///// get popular tag ///
	public function get_popular_tag(){
		$query = $this->db->query("SELECT ".PREFIX_TABLE."tags.name,key_tags,COUNT(*) AS num FROM ".PREFIX_TABLE."post_has_tags
		INNER JOIN ".PREFIX_TABLE."posts ON use_post_id=post_id INNER JOIN ".PREFIX_TABLE."tags ON tag_id = use_tag_id
		GROUP BY ".PREFIX_TABLE."tags.name ORDER BY COUNT(*) DESC ");

		return $query->num_rows>0?$query->result():NULL;
	}

	public function get_related_post($tag){
// 		$query = $this->db->query("select * from ".PREFIX_TABLE."posts inner join ".PREFIX_TABLE."tags on ".PREFIX_TABLE."posts.use_tag_Id = ".PREFIX_TABLE."tags.tag_id
// where ".PREFIX_TABLE."tags.tag_id = ".$tag." order by ".PREFIX_TABLE."posts.count_click_views desc limit 3;");
// 		return $query;
		$this->db->from(PREFIX_TABLE.'post_has_tags');
		$this->db->join(PREFIX_TABLE.'posts','use_post_id=post_id');
		$this->db->join(PREFIX_TABLE.'tags','tag_id = use_tag_id');
		$this->db->where(PREFIX_TABLE.'posts.embed_img !=',"");
		$this->db->or_where(PREFIX_TABLE.'posts.img_name !=',"");
		$this->db->where(PREFIX_TABLE.'tags.tag_id',$tag);
		$this->db->group_by('post_id');
		$this->db->order_by(PREFIX_TABLE.'posts.count_click_views','desc');
		$this->db->limit(3);
		return $this->db->get();
	}
}

